﻿using System;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

namespace VitCode.Castle.Helper
{
    /// <summary>
    /// Excel導入的協助類
    /// </summary>
    /// <example>
    /// <code>
    /// ExcelHelper eh = new ExcelHelper("Excel.xls", "Sheet1");
    /// //設定不能為空列索引
    /// eh.NotEmptyColumns.SetColumnIndex(0);
    /// //設定不能重複的列
    /// eh.NotRepleateColumns.SetColumnIndex(1);
    /// //設定不能重複的列名稱
    /// eh.NotEmptyColumns.SetColumnName("說明");
    /// //進行Check
    /// ErrorResult ers = eh.Check();
    /// if(ers.Rows.Count!=0)
    /// {
    ///    Console.WriteLine("hasn't not error");
    /// }
    /// else
    /// {
    ///    Console.WriteLine("has error");
    /// }
    /// </code>
    /// 
    /// </example>
    public sealed class ExcelHelper : DataTableCheckerHelper
    {
        private OleDbConnection m_Conn;

        /// <summary>
        /// 當每次檢查某行後的時候發生的事件
        /// </summary>
        public event ExcelHelperHandler CheckingEvent;

        /// <summary>
        /// 構建函數
        /// </summary>
        /// <param name="path">檔路徑</param>
        /// <param name="sheetName">表明</param>
        public ExcelHelper(string path, string sheetName)
        {
            if (path == null)
                throw new ArgumentNullException("path");
            if (sheetName == null)
                throw new ArgumentNullException("sheetName");

            DataSet ds = new DataSet();
            ds.Clear();

            //hh 2007-12-03
            string strPower = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="
                              + path + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
            //end

            m_Conn = new OleDbConnection(strPower);
            SheetName = sheetName;
        }

        DataTable m_SourceData;

        /// <summary>
        /// 構建函數2
        /// </summary>
        /// <param name="data"></param>
        public ExcelHelper(DataTable data)
        {
            m_SourceData=data;
        }

        /// <summary>
        /// 獲取或設置Excel中資料的第一行是否為欄位名稱
        /// </summary>
        /// <remarks>
        /// 如果設定為true，那麼訪問連接字串會帶有HDR=YES
        /// </remarks>
        public override bool HasTitle
        {
            get { return base.HasTitle; }
            set
            {
                base.HasTitle = value;
                if (!value)
                {
                    m_Conn.ConnectionString = m_Conn.ConnectionString.Replace("HDR=Yes;", "");
                }
            }
        }


        private string m_SheetName;
        private bool m_ColumnLengthError = true;

        /// <summary>
        /// 獲取或設定當前指定的SheetName
        /// </summary>
        public string SheetName
        {
            get { return m_SheetName; }
            set
            {
                if (value == null)
                    throw new ArgumentNullException("SheetName");
                if (value.Length == 0)
                    throw new ArgumentOutOfRangeException("SheetName", SheetName.Length, "SheetName長度不能為0");

                m_SheetName = value;
            }
        }

        #region 獲取DataTable


        /// <summary>
        /// 獲取一個新的記錄集（列的內容格式都為字串）
        /// </summary>
        /// <param name="source">源資料</param>
        /// <returns>內容一致（格式的字串）的記錄集</returns>
        private DataTable SetCheckedDataFormatToString(DataTable source)
        {
            DataTable tempDt = new DataTable();
            if(source != null && source.Rows.Count > 0)
            {
                for(int i=0;i<source.Columns.Count;i++)
                {
                    string columnsName = source.Columns[i].ColumnName;
                    tempDt.Columns.Add(columnsName,typeof(String));
                }
                for(int i=0;i<source.Rows.Count;i++)
                {
                    tempDt.Rows.Add(source.Rows[i].ItemArray);
                }
                return tempDt;
            }
            else
                return source;
        }

		
        /// <summary>
        /// 獲取某個Sheet的DataSet，裏面的TableName是Sheet的名稱
        /// </summary>
        /// <param name="strSheetName">SheetName</param>
        /// <returns></returns>
        public DataTable GetSheetDataSet(string strSheetName)
        {
            DataTable sourceFromSheet = new DataTable(strSheetName);
            string tempConnString = m_Conn.ConnectionString;
            if(tempConnString.IndexOf("HDR=Yes")>-1)
                m_Conn.ConnectionString = tempConnString.Replace("HDR=Yes","HDR=NO");//IMEX=1,這個設置只有在資料集的第一行為字元的情況下才有用,要是第一行資料喔非文本類型的資料就會有問題,而範本的列頭通常為字串,所以程式先以沒列頭方式讀取資料
            OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from [" + strSheetName + "$]", m_Conn);
            objAdapter.Fill(sourceFromSheet);
            objAdapter.Dispose();
			
            DataTable dsNoHead = SetCheckedDataFormatToString(sourceFromSheet);
			
            CharConvertHelper charHelper = new CharConvertHelper();
            charHelper.ToDBC(dsNoHead);//把資料源中的資料包含的全形字符也轉換成半形字符

            DataTable result = new DataTable();
            if(dsNoHead != null && dsNoHead.Rows.Count > 0)
            {
                for(int r=0;r<dsNoHead.Rows.Count;r++)
                {
                    DataRow row = dsNoHead.Rows[r];
                    if(r==0)//把第一行的資料內容作為列頭,因為上樣是以沒列頭方式讀取資料的
                    {
                        for(int c=0;c<dsNoHead.Columns.Count;c++)
                            result.Columns.Add(row[c].ToString(),typeof(String));
                    }
                    else
                        result.Rows.Add(row.ItemArray);
                }
            }
            m_Conn.ConnectionString = tempConnString;//還原成源來的字串

            if( this.m_AutoFillNotExistColumn && m_NotExistColumn!=null && m_NotExistColumn.Count>0)
            {
                //為列表補充不存在的列，自動填充列
                foreach(string str in m_NotExistColumn)
                    result.Columns.Add(str,typeof(String));
            }

            return result;
        }

        /// <summary>
        /// 獲取當前工作表的DataSet
        /// </summary>
        /// <returns></returns>
        public override DataTable GetDataTable()
        {
            if(m_SourceData!=null)
                return m_SourceData;
            else
                return GetSheetDataSet(SheetName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public override DataTable GetEmptyDataTableFromSource()
        {
            DataTable dt = new DataTable(SheetName);
            OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from [" + SheetName + "$] where 1<>1", m_Conn);
            objAdapter.Fill(dt);
            objAdapter.Dispose();
            return dt;
        }


        /// <summary>
        /// 設置或獲取如果列數目不相同(但是需要檢查的名稱全部相同)的情況下
        /// 是否報錯,默認為true
        /// </summary>
        public bool ColumnLengthError
        {
            get { return m_ColumnLengthError; }
            set { m_ColumnLengthError = value; }
        }

        #endregion


        #region 自動補充列功能
        private bool m_AutoFillNotExistColumn = false;
        /// <summary>
        /// 是否自動填充不存在的列
        /// 默認不自動填充
        /// </summary>
        public bool AutoFillNotExistColumn
        {
            get{return m_AutoFillNotExistColumn;}
            set{m_AutoFillNotExistColumn=value;}
        }

        /// <summary>
        /// 不存在的列
        /// 用於自動填充
        /// </summary>
        private StringCollection m_NotExistColumn = new StringCollection();
        /// <summary>
        /// 不存在的列
        /// 用於自動填充
        /// </summary>
        public StringCollection NotExistColumn
        {
            get{return m_NotExistColumn;}
            set{m_NotExistColumn=value;}
        }

        #endregion

        /// <summary>
        /// 檢查列標題
        /// </summary>
        /// <param name="strSheetName">Sheet名稱，不需要帶"$"號</param>
        /// <param name="columnName">輸入列民稱</param>
        /// <param name="errorMesasge">出錯資訊</param>
        /// <returns>如果符合標題那麼返回true，否則返回false</returns>
        public bool CheckTitle(string strSheetName, out string errorMesasge, params string[] columnName)
        {
            DataColumnCollection columnCollection = GetCheckTitles();//實際列名

            if (columnName.Length != columnCollection.Count && ColumnLengthError)
            {
                int realCount = 0;
                if (columnCollection.Count > 0 && columnCollection[0].ColumnName != "F1")
                    realCount = columnCollection.Count;
                StringBuilder sb = new StringBuilder("列的數量不匹配 預計列數" + columnName.Length + ",實際列數" + realCount);
                StringCollection sc = new StringCollection();

                StringCollection notinList = new StringCollection();//非法的列
                if (columnName.Length > columnCollection.Count && columnCollection[0].ColumnName != "F1")
                {
                    //範本預計列數大於實際列數
                    if(!m_AutoFillNotExistColumn) sb.Append(",其中在實際表中缺少的列為");
                    sc.AddRange(columnName);
                    foreach (DataColumn dataColumn in columnCollection)
                    {
                        if (sc.Contains(dataColumn.ColumnName))
                        {
                            sc.Remove(dataColumn.ColumnName);
                        }
                        else
                        {
                            notinList.Add(dataColumn.ColumnName);
                        }
                    }
                    if(notinList.Count>0 && m_AutoFillNotExistColumn)
                    {
                        sb = new StringBuilder();
                        sb.Append("其中實際表中不正確的列名為");
                        foreach (string s in notinList)
                        {
                            sb.Append("\"");
                            sb.Append(s);
                            sb.Append("\"");
                        }
                        errorMesasge = sb.ToString();
                        return false;
                    }else if(m_AutoFillNotExistColumn)
                    {
                        //缺少就不報錯了，當自動填充列的時候
                        //m_NotExistColumn ;
                        foreach (string s in sc)
                        {
                            m_NotExistColumn.Add(s);
                        }
                        errorMesasge = null;
                        return true;
                    }
                }
                else
                {
                    sb.Append(",其中在實際表中多出的列為");
                    foreach (DataColumn dc in columnCollection)
                    {
                        if (Array.IndexOf(columnName, dc.ColumnName) == -1)
                        {
                            sc.Add(dc.ColumnName);
                        }
                    }
                }
                foreach (string s in sc)
                {
                    sb.Append("\"");
                    sb.Append(s);
                    sb.Append("\"");
                }
                errorMesasge = sb.ToString();
                return false;
            }

            for (int i = 0; i < columnName.Length; i++)
            {
                if (columnCollection[i].Caption != columnName[i])
                {
                    errorMesasge = "第" + i + "列的名稱不匹配,Excel表中的為\"" + columnCollection[i] + "\",要求是\"" + columnName[i] + "\"";
                    return false;
                }
            }
            errorMesasge = null;
            return true;
        }

        /// <summary>
        /// 檢查資料集合
        /// </summary>
        /// <returns></returns>
        public ErrorResult Check()
        {
            return Check(SheetName);
        }

        /// <summary>
        /// 檢查DataTable格式
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>返回檢查結果</returns>
        public ErrorResult Check(DataTable dt)
        {
            ErrorResult errorResult = new ErrorResult();

            if( this.m_AutoFillNotExistColumn && m_NotExistColumn!=null && m_NotExistColumn.Count>0)
            {
                //為列表補充不存在的列，自動填充列
                foreach(string str in m_NotExistColumn)
                    if(!dt.Columns.Contains(str))
                        dt.Columns.Add(str,typeof(String));
            }

            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
            {
                DataRow dr = dt.Rows[rowIndex];
                NotEmptyColumns.CheckIt(dr, rowIndex, errorResult);
                NotRepleateColumns.CheckIt(dr, rowIndex, errorResult);

                if (CheckingEvent != null)
                    CheckingEvent(dr, errorResult, GetSourceDataIndex(rowIndex));
            }
            return errorResult;
        }

        /// <summary>
        /// 根據設定，開始價差Excel標中Sheet的格式是否正確
        /// </summary>
        /// <param name="sheetName">表名稱</param>
        /// <returns></returns>
        public ErrorResult Check(string sheetName)
        {
            return Check(GetSheetDataSet(sheetName));
        }

        /// <summary>
        /// 把資料倒入到ExcelFile裏面
        /// </summary>
        /// <param name="dt">資料集合</param>
        /// <param name="tempExcelFile">提供一個用於生成檔的模版檔</param>
        /// <param name="sheetName">表名稱</param>
        /// <returns>返回生成檔的路徑</returns>
        public string Insert(DataTable dt, string tempExcelFile, string sheetName)
        {
            string filePath = Path.GetTempFileName();
            File.Copy(tempExcelFile, filePath, true);
            File.SetAttributes(filePath, FileAttributes.Normal);

            string strPower = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="
                              + filePath + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;""";
            if (!HasTitle)
                strPower = strPower.Replace("HDR=Yes;", "");
            OleDbConnection conn = new OleDbConnection(strPower);


            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + sheetName + "$]", conn);
            adapter.TableMappings.Add("[" + sheetName + "$]", dt.TableName);
            OleDbCommandBuilder od = new OleDbCommandBuilder(adapter);
            od.QuotePrefix = "[";
            od.QuoteSuffix = "]";
            adapter.Update(dt);

            return filePath;
        }
    }
}
